﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataAccessLayer
{
    public class SupplierDataAccess
    {
        StationeryInventoryEntities1 r;

        public SupplierDataAccess()
        {
            r = new StationeryInventoryEntities1();
        }

        public List<string> getSupplierid()
        {
            List<string> idlist = new List<string>();
            var x = from s in r.Suppliers
                    select s.SupplierID;
            idlist = x.ToList<string>();

            return idlist; 
        }

        public bool savesupplier(String SupplierID, String Suppliername, String ContactName, int Phoneno, int Faxno, String GSTregno, String address, String email)
        {
            bool ans = false;
            Supplier sup = new Supplier();
            sup.SupplierID = SupplierID;
            sup.SupplierName = Suppliername;
            sup.ContactName = ContactName;
            sup.PhoneNo = Phoneno;
            sup.FaxNo = Faxno;
            sup.GSTRegNo = GSTregno;
            sup.Address = address;
            sup.Email = email;
            try
            {
                r.Suppliers.Add(sup);
                r.SaveChanges();
                ans = true;
            }
            catch (Exception ex)
            {
                ans = false;  
            }
            return ans;   
        }

        public void saveNewSupplier(Supplier supplier)
        {
            r.Suppliers.Add(supplier);
            r.SaveChanges();
        }

        public List<object> category_search(int search)
        {
            List<object> stationerylist = new List<object>();

            StationeryInventoryEntities1 se = new StationeryInventoryEntities1();
            var q = from st in se.Stationeries
                    join c in se.Categories on st.CategoryID equals c.CategoryID
                    where c.CategoryID == search
                    select new { st.ItemCode, c.CategoryName, st.ItemName, st.ReorderLevel, st.ReorderQty, st.UOM, st.AvailableQty };
            stationerylist = q.ToList<object>();
            return stationerylist;
        }

        public List<object> category_search(string search)
        {
            List<object> stationerylist = new List<object>();

            StationeryInventoryEntities1 se = new StationeryInventoryEntities1();
            var q = from st in se.Stationeries
                    join c in se.Categories on st.CategoryID equals c.CategoryID
                    where c.CategoryName == search
                    select new { st.ItemCode, c.CategoryName, st.ItemName, st.ReorderLevel, st.ReorderQty, st.UOM, st.AvailableQty };
            stationerylist = q.ToList<object>();
            return stationerylist; 
        }

        public List<object> category_searchall()
        {
            List<object> stationerylist = new List<object>();

            StationeryInventoryEntities1 se = new StationeryInventoryEntities1();
            var q = from st in se.Stationeries
                    join c in se.Categories on st.CategoryID equals c.CategoryID
                    select new { st.ItemCode, c.CategoryName, st.ItemName, st.ReorderLevel, st.ReorderQty, st.UOM, st.AvailableQty };
            stationerylist = q.ToList<object>();
            return stationerylist;
        }

        public Stationery getStationery(string itemCode)
        {
            Stationery stationery = new Stationery();

            var stat = r.Stationeries.FirstOrDefault(x => x.ItemCode == itemCode);

            if (stat != null)
            {
                stationery = stat;
            }

            return stationery;
        }

        public List<SupplierDetail> getSupplierDetailByItemCode(string itemCode)
        {
            List<SupplierDetail> list = new List<SupplierDetail>();
            var supplierList = (from x in r.SupplierDetails
                                                 where x.ItemCode == itemCode
                                                 orderby x.Rank
                                                 select x).ToList();
            if(supplierList != null)
            {
                list = supplierList;
            }
            return list;
        }

        public List<StockHistory> getStockHistoryByItemCode(string itemCode)
        {
            List<StockHistory> list = new List<StockHistory>();

            var m = from st in r.StockHistories
                    where st.ItemCode == itemCode
                    select st;

            if (m != null)
            {
                list = m.ToList();
            }
            return list;
        }

        public List<StockHistory> getStockHistoryByDate(List<StockHistory> stockHistory, DateTime startDate, DateTime endDate)
        {
            List<StockHistory> list = new List<StockHistory>();

            var n = from ss in stockHistory
                    where ss.Date >= startDate && ss.Date <= endDate
                    select ss;

            if (n != null)
            {
                list = n.ToList();
            }
            return list;
        }

        public void AddStationery(Stationery stationery)
        {
            r.Stationeries.Add(stationery);
            r.SaveChanges();
        }

        public void AddSupplierDetail(SupplierDetail supplierDetail)
        {
            r.SupplierDetails.Add(supplierDetail);
            r.SaveChanges();
        }

        public List<Object> getcategory()
        {
            List<object> categories = new List<object>();
            var q = from x in r.Categories
                    select x;
            categories = q.ToList<object>();
            return categories;       
        }

        public List<object> getCatItems(int category)
        {
            List<object> catItems = new List<object>();
            var q = (from x in r.Stationeries
                     where x.CategoryID == category
                     select x);
            catItems = q.ToList<object>();
            return catItems;
        }

        public List<Object> getSuppliers()
        {
            List<Object> supplist = new List<object>();
            var q = from s in r.Suppliers
                    select s;
            supplist = q.ToList<Object>();
            return supplist;  
        }

        public List<Employee> getemployeelist() 
        {
            List<Employee> emp = r.Employees.Where(x => x.EmployeeID == x.Department.RepresentativeID).ToList();
            return emp;              
        }

        public Employee getemployee(int empId)
        {
            Employee emp = r.Employees.FirstOrDefault(x => x.EmployeeID == empId);
            return emp; 
        }

        public CollectionPointDetail getcollectiontime(DateTime time)
        {
            CollectionPointDetail coll_time = r.CollectionPointDetails.FirstOrDefault(x => x.CollectionTime == time);
            return coll_time;                  
        }

        public CollectionPointDetail getcollectiondate(DateTime date)
        {
            CollectionPointDetail coll_date = r.CollectionPointDetails.FirstOrDefault(x => x.CollectionDate == date);
            return coll_date;
        }

        public List<object> getSelectedSupplierDetail(string itemCode)
        {
            List<object> list = new List<object>();

            var q = from st in r.SupplierDetails
                    join c in r.Suppliers on st.SupplierID equals c.SupplierID
                    where st.ItemCode == itemCode
                    select new { st.Stationery.ItemName, st.Rank, c.SupplierName, c.PhoneNo, st.Price };

            if (q != null)
            {
                list = q.ToList<object>();
            }

            return list;
        }

        public Supplier GetSupplierBySupplierId(string supplierId)
        {
            Supplier supplier = new Supplier();

            var sup = r.Suppliers.FirstOrDefault(x => x.SupplierID == supplierId);

            if (sup != null)
            {
                supplier = sup;
            }
            return supplier;
        }

    }
}
